-- 删除表（如果存在）
DROP TABLE IF EXISTS t_admin;

-- 创建表
CREATE TABLE IF NOT EXISTS t_admin(
    id SERIAL PRIMARY KEY,
    username varchar(255) NOT NULL UNIQUE,
    password varchar(255) NOT NULL default '',
    nickname varchar(255) NOT NULL default '',
    phone varchar(20) NOT NULL default '',
    email varchar(50) NOT NULL default '',
    lastUpdateTime timestamp DEFAULT NULL,
    roleId int NOT NULL,
    status smallint NOT NULL default 1,
    createTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modifyTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    creatorId int NOT NULL,
    creatorName varchar(255) not null,
    modifierId int NOT NULL,
    modifierName varchar(255) not null
    );

-- 为每个字段添加注释
COMMENT ON TABLE t_admin IS '管理员管理';
COMMENT ON COLUMN t_admin.username IS '用户名';
COMMENT ON COLUMN t_admin.password IS '密码';
COMMENT ON COLUMN t_admin.nickname IS '昵称';
COMMENT ON COLUMN t_admin.phone IS '手机';
COMMENT ON COLUMN t_admin.email IS '电子邮箱';
COMMENT ON COLUMN t_admin.lastUpdateTime IS '最后登陆时间';
COMMENT ON COLUMN t_admin.roleId IS '角色ID';
COMMENT ON COLUMN t_admin.status IS '状态';
COMMENT ON COLUMN t_admin.createTime IS '创建时间';
COMMENT ON COLUMN t_admin.modifyTime IS '更新时间';
COMMENT ON COLUMN t_admin.creatorId IS '创建人ID';
COMMENT ON COLUMN t_admin.creatorName IS '创建人名称';
COMMENT ON COLUMN t_admin.modifierId IS '修改人ID';
COMMENT ON COLUMN t_admin.modifierName IS '修改人名称';

-- 创建更新时间的触发器函数
CREATE OR REPLACE FUNCTION update_modify_time()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modifyTime = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 为表添加更新时间触发器
CREATE TRIGGER t_admin_modify_time_trigger
    BEFORE UPDATE ON t_admin
    FOR EACH ROW
    EXECUTE FUNCTION update_modify_time();

-- 插入初始数据
INSERT INTO t_admin(id, username, password, roleId, creatorId, creatorName, modifierId, modifierName)
VALUES(1000, 'admin', '$2a$10$NIkz3yq8nYIUApwKMG2Ux.KxAtesj2JTqILwmW2ZUELKf0jQCpNVK', 1000, 1000, 'admin', 1000, 'admin');
-- 密码:qwer1234!

-- 重置序列值，确保下一次自增从1001开始
SELECT setval('t_admin_id_seq', 1000);
